import time
from .mysqlconn import conn
from .mongodbconnection import mongodbcon
from .mongodbconnection import mongorobot


#查询包含的公司编码
def getcmpid(dbname,cid):
    sql = " SELECT c.COMPANYID from zt_company c  ORDER BY c.COMPANYID ;"
    cmpidlist = conn.mysql_sel(sql,dbname,cid)
    return cmpidlist


#写入文本记录
def writetxt(file,text):
    file_object = open("./"+ file +"/" + time.strftime("%Y-%m-%d")+".txt", "a") 
    try:
        file_object.write(""+ text +"\n")
        print(text)
    finally:  
        file_object.close() 

        
#查询公司的问题库
def selquestion(cmpid,dbname,cid):
    sql = " select kq.question,CONCAT('A',ka.answerid) from zt_know_question kq,zt_know_answer ka where  \
           ka.answerid=kq.answerid and kq.state=1 and kq.cmpid='" +str(cmpid)+ "' \
           union all \
           SELECT sq.question,CONCAT('A',kq.answerid) from zt_know_samequestion sq,zt_know_answer ka ,zt_know_question kq  \
           where sq.quesid=kq.quesid and ka.answerid=kq.answerid and kq.state=1 and kq.cmpid='" +str(cmpid)+ "' \
            union all \
           select s.question,s.answerid from zy_robotstudy s where s.cmpid='" +str(cmpid)+ "' and s.state=1 "
    question = conn.mysql_sel(sql,dbname,cid)
    return question


#插入公司机器人更新记录
def updatarobot(cmpid, suf,dbname,cid):
    sql = " insert into  zy_robotupdate(cmpid,updatadata,suf) VALUES ('"+str(cmpid)+"',NOW(),'"+str(suf)+"'); "
    count = conn.mysql_insert(sql,dbname,cid)
    return count

#and (TO_DAYS(NOW()) -TO_DAYS(ct.sendtime))=1 



#重新导入问题库
# while True:
#     try :
#         current_time = time.localtime(time.time())
#         if((current_time.tm_hour == 17) and  \
#            (current_time.tm_min == 26) and (current_time.tm_sec == 00)):
#             writetxt('logstudyquestion',"\n\n\n" + str(time.strftime('%Y-%m-%d %H-%M-%S')))
#             writetxt('logstudyquestion','begin===============更新问题库表==============================')
#             cmpidlist = getcmpid(dbname,cid)
#             #获取公司编码
#             if len(cmpidlist) > 0:
#                 for cmpid in cmpidlist:
#                     writetxt('logstudyquestion',time.strftime('%Y-%m-%d %H-%M-%S') + "公司编码："+ str(cmpid[0]))
#                     try:
#                         #删除表
#                         mongodbcon.dropTable('database'+ str(cmpid[0]))
#                         writetxt('logstudyquestion',"已经删除了表"+ 'database'+ str(cmpid[0]))
#                         #获取问题
#                         questionlist = selquestion(cmpid[0])
#                         writetxt('logstudyquestion',"存储数量：" + str(len(questionlist)) )
#                         if len(questionlist) > 0:
#                             for question in questionlist :
#                                 mongorobot.addstatement(cmpid[0],question[0],question[1],dbname)
#                         count = updatarobot(cmpid[0], 1)
#                         if count>0 and count!=conn.err:
#                             pass
#                         else:
#                             writetxt('logstudyquestion','插入数据错误，公司编码'+ "公司编码：" + str(cmpid[0]))
#
#                     except:
#                         writetxt('logstudyquestion',"执行学习任务错误！！")
#                         updatarobot(cmpid[0], 2)
#
#
#             else :
#                 writetxt('logstudyquestion','没有公司需要学习！')
#             writetxt('logstudyquestion','end=============================================================')
#     except:
#          writetxt('logstudyquestion','机器人学习问题库出错了！！')
#          writetxt('logstudyquestion','end=============================================================')
#

                    
#
# def cmpidadd(cmpid):
#     writetxt('logstudyquestion',time.strftime('%Y-%m-%d %H-%M-%S') + "公司编码："+ str(cmpid))
#     try:
#         #删除表
#         mongodbcon.dropTable('database'+ str(cmpid))
#         writetxt('logstudyquestion',"已经删除了表"+ 'database'+ str(cmpid))
#         #获取问题
#         questionlist = selquestion(cmpid)
#         writetxt('logstudyquestion',"存储数量：" + str(len(questionlist)) )
#         if len(questionlist) > 0:
#             for question in questionlist :
#                 mongorobot.addstatement(cmpid,question[0],question[1],dbname)
#         count = updatarobot(cmpid, 1)
#         if count>0 and count!=conn.err:
#             pass
#         else:
#             writetxt('logstudyquestion','插入数据错误，公司编码'+ "公司编码：" + str(cmpid))
#
#     except:
#         writetxt('logstudyquestion',"执行学习任务错误！！")
#         updatarobot(cmpid, 2)
